# -*- coding: utf-8 -*-

import re
import time
import sqlite3
import datetime


class Log():

    file_name = "Log.db"
    conn = None

    def __init__(self):
        self.load()
        self.initDatebase()


    def load(self):
        """
        加载Log.db，初始化操作符
        """
        try:
            self.conn =  sqlite3.connect(self.file_name)
            self.conn_c = self.conn.cursor()
        except Exception as e:
            print(e)
            self.log = []


    def initDatebase(self):
        """
        如果Log.db中不存在events表，则创建
        """
        sql = "CREATE TABLE IF NOT EXISTS events(time_stamp DATETIME PRIMARY KEY, event TEXT,duration INTEGER)"
        self.conn_c.execute( sql )
        self.conn.commit()


    def append(self, item_name, spend_time):
        """
        向events表中添加记录

        Parameters:
            item_name: 事项名称
            spend_time: 第二个元素是在此事项所花费的分钟数
        """
        sql = "INSERT INTO events values(?,?,?)"
        t = datetime.datetime.now()
        t_str = t.strftime("%Y-%m-%d %H:%M:%S")
        self.conn_c.execute(sql, (t_str, item_name, spend_time))
        self.conn.commit()
        print("[Append] ", t_str, '"'+item_name+'"', spend_time)


    def appendWithTime(self, item_name, spend_time, add_time):
        """
        向events表中添加指定时间的记录

        Parameters:
            item_name: 事项名称
            spend_time: 在此事项所花费的分钟数
            add_time: 添加时间
        """
        pattern = re.compile(r'^(0[0-9]|1[0-9]|2[0-3]):(0[0-9]|[1-5][0-9])$')
        if not pattern.match(add_time):
            print("[Append] <Add_Time> 参数错误，应为 hh:mm")
            return
        sql = "INSERT INTO events values(?,?,?)"
        t = datetime.datetime.now()
        t_str = t.strftime("%Y-%m-%d ")
        t_str = t_str + add_time + ":00"
        self.conn_c.execute(sql, (t_str, item_name, spend_time))
        self.conn.commit()
        print("[Append] ", t_str, '"'+item_name+'"', spend_time)


    def getProcess(self, interval):
        """
        获取当前时间在一短时间内进度的倒数

        Parameters:
            interval: daily、weekly、monthly

        Return:
            float
        """
        range_start, range_end = self.getRange(interval)
        # print(range_start)
        # print(range_end)

        now_time = time.time()
        start_time = time.mktime(time.strptime(range_start, "%Y-%m-%d %H:%M:%S"))
        deadline = time.mktime(time.strptime(range_end, "%Y-%m-%d %H:%M:%S"))

        return (deadline-start_time)/(deadline-now_time)


    def getRange(self, interval):
        """
        获取当前时间的 天、周、月 的开始时间和结束时间

        Parameters:
            interval: daily、weekly、monthly

        Return:
            (start_time_str, end_time_str)
        """
        now_day = datetime.datetime.now()
        if interval == "daily":
            now_day_str = now_day.strftime("%Y-%m-%d")
            range_start = now_day_str + " 00:00:00"
            range_end = now_day_str + " 23:59:59"
        elif interval == "weekly":
            monday = now_day + datetime.timedelta(days=(1-now_day.isoweekday()))
            range_start = monday.strftime("%Y-%m-%d") + " 00:00:00"
            sunday = now_day + datetime.timedelta(days=(7-now_day.isoweekday()))
            range_end = sunday.strftime("%Y-%m-%d") + " 24:00:00"
        else:
            range_start = now_day.strftime("%Y-%m") + "-01 00:00:00"
            if now_day.month == 12:
                range_end = str(now_day.year+1) +"-01-01 00:00:00"
            else:
                range_end = now_day.strftime("%Y-") + "{:0>2d}".format(now_day.month+1) + "-01 00:00:00"

        return (range_start, range_end)


    def getSpecifiedRange(self, now_day, interval):
        """
        获取指定时间的 天、周、月 的开始时间和结束时间

        Parameters:
            interval: daily、weekly、monthly

        Return:
            (start_time_str, end_time_str)
        """
        if interval == "daily":
            now_day_str = now_day.strftime("%Y-%m-%d")
            range_start = now_day_str + " 00:00:00"
            range_end = now_day_str + " 23:59:59"
        elif interval == "weekly":
            monday = now_day + datetime.timedelta(days=(1-now_day.isoweekday()))
            range_start = monday.strftime("%Y-%m-%d") + " 00:00:00"
            sunday = now_day + datetime.timedelta(days=(7-now_day.isoweekday()))
            range_end = sunday.strftime("%Y-%m-%d") + " 24:00:00"
        else:
            range_start = now_day.strftime("%Y-%m") + "-01 00:00:00"
            if now_day.month == 12:
                range_end = str(now_day.year+1) +"-01-01 00:00:00"
            else:
                range_end = now_day.strftime("%Y-") + "{:0>2d}".format(now_day.month+1) + "-01 00:00:00"

        return (range_start, range_end)


    def getLog(self, range_start, range_end):
        """
        根据开始时间和结束时间，获取记录

        Parameters:
            range_start: str
            range_end: str

        Return:
            list
        """
        sql = "SELECT * FROM events WHERE time_stamp>=? and time_stamp<=?"
        self.conn_c.execute(sql, (range_start, range_end))
        fetch = self.conn_c.fetchall()
        return fetch


    def getUse(self, range_start, range_end):
        """
        根据开始时间和结束时间，统计期间一共花费了多少秒

        Parameters:
            range_start: str
            range_end: str

        Return:
            int
        """
        # 查询数据库
        total_use = 0
        fetch = self.getLog(range_start, range_end)

        for line in fetch:
            total_use = total_use + line[2]

        # print(event_name, "  ", total_use)
        return total_use


    def getEventUsedTime(self, event_name, range_start, range_end):
        """
        根据开始时间和结束时间，统计在某一事项上的秒数

        Parameters:
            event_name: str
            range_start: str
            range_end: str

        Return:
            int
        """
        # 查询数据库
        total_use = 0
        sql = "SELECT * FROM events WHERE event==? and time_stamp>=? and time_stamp<=?"
        self.conn_c.execute(sql, (event_name, range_start, range_end))
        fetch = self.conn_c.fetchall()

        for line in fetch:
            total_use = total_use + line[2]

        # print(event_name, "  ", total_use)
        return total_use


    def getDailyUsedTime(self, event_name):
        """
        获取这今天在某一事项花费的秒数

        Parameters:
            event_name: str

        Return:
            int
        """
        range_start, range_end = self.getRange("daily")
        return self.getEventUsedTime(event_name, range_start, range_end)


    def getWeeklyUsedTime(self, event_name):
        """
        获取这本周在某一事项花费的秒数

        Parameters:
            event_name: str

        Return:
            int
        """
        range_start, range_end = self.getRange("weekly")
        return self.getEventUsedTime(event_name, range_start, range_end)


    def getMonthlyUsedTime(self, event_name):
        """
        获取这本月在某一事项花费的秒数

        Parameters:
            event_name: str

        Return:
            int
        """
        range_start, range_end = self.getRange("monthly")
        return self.getEventUsedTime(event_name, range_start, range_end)


    def getEventUsedNum(self, event_name, range_start, range_end):
        """
        根据开始时间和结束时间，统计在某一事项上的次数

        Parameters:
            event_name: str
            range_start: str
            range_end: str

        Return:
            int
        """
        # 查询数据库
        sql = "SELECT * FROM events WHERE event==? and time_stamp>=? and time_stamp<=?"
        self.conn_c.execute(sql, (event_name, range_start, range_end))
        fetch = self.conn_c.fetchall()

        return len(fetch)


    def getDailyUsedNum(self, event_name):
        """
        获取这今天在某一事项花费的次数

        Parameters:
            event_name: str

        Return:
            int
        """
        range_start, range_end = self.getRange("daily")
        return self.getEventUsedNum(event_name, range_start, range_end)


    def getWeeklyUsedNum(self, event_name):
        """
        获取这本周在某一事项花费的次数

        Parameters:
            event_name: str

        Return:
            int
        """
        range_start, range_end = self.getRange("weekly")
        return self.getEventUsedNum(event_name, range_start, range_end)


    def getMonthlyUsedTime(self, event_name):
        """
        获取这本月在某一事项花费的次数

        Parameters:
            event_name: str

        Return:
            int
        """
        range_start, range_end = self.getRange("monthly")
        return self.getEventUsedNum(event_name, range_start, range_end)


    def getItemDetails(self, event_name):
        """
        获取某一事项的所有记录

        Parameters:
            event_name: 事项名称（name + start_time date only + %）

        Return:
            list
        """
        sql = "SELECT * FROM events WHERE event LIKE '{}'".format( event_name )
        # print("[sql]", sql)
        self.conn_c.execute(sql.strip())
        fetch = self.conn_c.fetchall()
        return fetch


    def getItemToday(self, event_name):
        """
        获取某一事项在今天的记录

        Parameters:
            event_name: str

        Return:
            list
        """
        range_start, range_end = self.getRange("daily")
        sql = "SELECT * FROM events WHERE event==? and time_stamp>=? and time_stamp<=?"
        # print("[sql]", sql)
        self.conn_c.execute(sql, (event_name, range_start, range_end))
        fetch = self.conn_c.fetchall()
        return fetch


    def getAllItemToday(self):
        """
        获取今天所有的记录

        Return:
            list
        """
        range_start, range_end = self.getRange("daily")
        sql = "SELECT * FROM events WHERE time_stamp>=? and time_stamp<=?"
        # print("[sql]", sql)
        self.conn_c.execute(sql, (range_start, range_end))
        fetch = self.conn_c.fetchall()
        return fetch


    def getDetailsOfThePastDay(self, n):
        """
        获取过去某一天的所有记录

        Parameters:
            n: int （正整数）

        Return:
            list
        """
        now_day = datetime.datetime.now()
        temp_day = now_day + datetime.timedelta(days=n)
        range_start, range_end = self.getSpecifiedRange(temp_day, "daily")

        return self.getLog(range_start, range_end)


    def write(self):
        self.conn.close()


    def __del__(self):
        self.write()